package dao.impl;

import dao.BaseDao;
import dao.DormitoryDao;
import entity.Dormitory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DormitoryDaoImpl extends BaseDao implements DormitoryDao {

    @Override
    public List<Dormitory> cx1(Integer lid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dormitory> dormBuildings = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder("SELECT building_id, building_name, address, manager_id, total_floors FROM dorm_buildings WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND building_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();

            while (rs.next()) {
                Dormitory dormBuilding = new Dormitory();
                dormBuilding.setBuilding_id(rs.getInt("building_id"));
                dormBuilding.setBuildingname(rs.getString("building_name"));
                dormBuilding.setAddress(rs.getString("address"));
                dormBuilding.setManager_id(rs.getInt("manager_id"));
                dormBuilding.setTotal_floors(rs.getInt("total_floors"));
                dormBuildings.add(dormBuilding);
            }
            return dormBuildings;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int sc(int id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "DELETE FROM dorm_buildings WHERE building_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int tj(String building_name, String address, int manager_id, int total_floors) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "INSERT INTO dorm_buildings (building_name, address, manager_id, total_floors) VALUES (?, ?, ?, ?)";
            pst = conn.prepareStatement(sql);
            pst.setString(1, building_name);
            pst.setString(2, address);
            pst.setInt(3, manager_id);
            pst.setInt(4, total_floors);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int xg(String building_name, String address, int manager_id, int total_floors, int building_id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;

        try {

            String sql = "UPDATE dorm_buildings SET building_name = ?, address = ?, manager_id = ?, total_floors = ? WHERE building_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, building_name);
            pst.setString(2, address);
            pst.setInt(3, manager_id);
            pst.setInt(4, total_floors);
            pst.setInt(5, building_id);
            return pst.executeUpdate();
        } finally {
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Dormitory> cx(Integer lid, int page, int pageSize) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Dormitory> dormBuildings = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder("SELECT building_id, building_name, address, manager_id, total_floors FROM dorm_buildings WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND building_id = ?");
            }
            sql.append(" LIMIT ?, ?");

            pst = conn.prepareStatement(sql.toString());
            int paramIndex = 1;

            if (lid != null && lid > 0) {
                pst.setInt(paramIndex++, lid);
            }

            int startPage=page*pageSize;
            pst.setInt(paramIndex++, startPage);
            pst.setInt(paramIndex, pageSize);

            rs = pst.executeQuery();

            while (rs.next()) {
                Dormitory dormBuilding = new Dormitory();
                dormBuilding.setBuilding_id(rs.getInt("building_id"));
                dormBuilding.setBuildingname(rs.getString("building_name"));
                dormBuilding.setAddress(rs.getString("address"));
                dormBuilding.setManager_id(rs.getInt("manager_id"));
                dormBuilding.setTotal_floors(rs.getInt("total_floors"));
                dormBuildings.add(dormBuilding);
            }
            return dormBuildings;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int getTotalCount(Integer lid) throws SQLException, ClassNotFoundException {
       getConnection();
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM dorm_buildings WHERE 1=1");

            if (lid != null && lid > 0) {
                sql.append(" AND building_id = ?");
            }

            pst = conn.prepareStatement(sql.toString());

            if (lid != null && lid > 0) {
                pst.setInt(1, lid);
            }

            rs = pst.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (pst != null) pst.close();
            if (conn != null) conn.close();
        }
    }
}